Previous Topic

Next Topic

Checking for Duplicate Records

You can create custom duplicate match key formulas to prevent the entry of duplicate customer records in the Name table. One formula, the Duplicate Record Check Formula, affects records entered anywhere in iMIS except in the Customer Portfolio. The CP Duplicate Record Check Formula is specific to the Customer Portfolio. In order for duplicates to be detected properly, these two formulas must be equivalent.

Your formulas can combine fields or portions of fields to compare a record being entered against records that already exist in the database. When a possible match is found, iMIS presents a match list and warns the data entry user that the record may be a duplicate. Based on this information, the user can continue or can cancel the insertion.

Default Formulas

The default CP Duplicate Record Check Formula specifies the same criteria as the Duplicate Record Check Formula, but uses SQL instead of Omnis.

By default, the Duplicate Record Check Formula field contains the following Omnis formula:

upp(con(mid(Name.ZIP,1,5),pick(Name.LAST_NAME<>'',mid(Name.COMPANY,1,5),con(mid(Name.LAST_NAME,1,4),mid(Name.FIRST_NAME,1,1)))))

By default, the CP Duplicate Record Check Formula field contains the following SQL formula:

UPPER(SUBSTRING(Name.ZIP,1,5) + (CASE WHEN Name.LAST_NAME<>'' THEN (SUBSTRING(Name.LAST_NAME,1,4) + SUBSTRING(Name.FIRST_NAME,1,1)) ELSE SUBSTRING(Name.COMPANY,1,5) END))

Caution! If you customize the Duplicate Record Check Formula and CP Duplicate Record Check Formula ensure that the formulas contain equivalent selection criteria. Although it is possible to specify different criteria in these fields, if you do not use equivalent formulas, identical records entered through different modules will end up with different duplicate match keys and will therefore not be identified by iMIS as potential duplicates.

When entering a custom formula in the CP Duplicate Record Check Formula field, the formula can contain up to 255 characters, and the resulting duplicate match key can contain up to 20 characters. You can use the following Name fields when entering a custom duplicate check formula in the CP Duplicate Record Check Formula field:

Name.FIRST_NAME

Name.LAST_NAME

Name.COMPANY

Name.ZIP

Name.CITY

Name.STATE_PROVINCE

Name.COUNTRY

Name.ORG_CODE

Name.MEMBER_TYPE

Name.MAJOR_KEY

Name.WORK_PHONE

Name.HOME_PHONE

Name.FAX

Name.EMAIL

Name.COUNTY

Name.TOLL_FREE

Name.CHAPTER

Name.JOIN_DATE

Name.BIRTH_DATE

Name.GENDER

Note: This facility cannot protect against all data-entry problems, such as address changes, variable name spellings, and typing errors. For best results, use standard lookup before adding questionable database records.

In This Section

Example Duplicate Record Check Formulas

See Also

Customer System Setup

Customer Setup Overview

Set up general options window

Customizing the iMIS Desktop

To customize the default Tab Preferences profile

Setting Basic Customer Options

Setting Customer Address and Notes

Setting Accumail Options

Synchronizing Phone Numbers and E-mail Addresses

Defining Lookup Indexes for the Find Window

Implementing and Managing Activity Types

Customer General Lookup/Validation and Expansion tables

Defining State/Province Codes

Setting up Login Activity Tasks

Adding and Deleting Fields in the Change Log

Using International Address Formatting

Setting up Acquisition Management

Setting Advanced Options


ASI logo 10.6 Production Release. Updated 1/18/2006 3:26:46 PM
© Copyright by Advanced Solutions International, Inc. All rights reserved.